package Test3;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.List;
import java.util.ResourceBundle;



public class JDBCUile {

	private static ResourceBundle rb = ResourceBundle.getBundle("Test3.jdbc");
	private static String classname = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");

	static {
		try {
			Class.forName(classname);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动加载失败");
			e.printStackTrace();
		}

	}

	public static void exClose(ResultSet resultSet, Statement statement, Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 获得连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
			// System.out.println("数据库连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;

	}

	// 初始化 停车场状态 插入初始化停车场信息
	public static void insertParkingState() {
		String sql1 ="delete from parking";
		
		
		String sql2 = "insert into parking(space_id,state) values(?,'空闲')";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = connection.prepareStatement(sql1);
			ps.executeUpdate();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		
		ps=null;
		
		

		try {
			ps = connection.prepareStatement(sql2);
			connection.setAutoCommit(false);
			for (int i = 1; i <= 200; i++) {
				ps.setInt(1, i);
				ps.addBatch();

			}
			ps.executeBatch();
			connection.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// 插入 车辆信息

	public void insertSingleCar(Car car) {
		String sql = "insert into car (car_id,high)values(?,?)";
		String carId = car.getId();
		double hight = car.getHigh();

		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, carId);
			ps.setDouble(2, hight);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 查询车辆高度
	public static double selectCarHigh(String carId) {
		String sql = "select high from car where car_id=?";

		Connection connection = getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		double high = 0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, carId);
			rs = ps.executeQuery();

			while (rs.next()) {
				high = rs.getDouble("high");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return high;

	}

	// 插入停车记录
	public void insertSingleRecord(Record record) {
		String carId = record.getCarId();
		int spaceId = record.getSpaceId();
		String timeStart = record.getTimeStart();
		String timeEnd = record.getTimeEnd();
		double cost = record.getCost();
		String sql = "insert into record (car_id,space_id,time_start,time_end,cost)values(?,?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			ps.setString(1, carId);
			ps.setInt(2, spaceId);
			ps.setString(3, timeStart);
			ps.setString(4, timeEnd);
			ps.setDouble(5, cost);

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 统计空闲车位
	public static int selectAllSpace() {
		String sql = "select count(space_id) as count from parking \r\n" + "where state =\"空闲\"";

		Connection connection = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sum = 0;

		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				sum = rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return sum;

	}

	// 更新停车场状态
	public static void updateSpaceState(String state, Integer spaceId) {
		String sql = "update parking set state =? where space_id=?";

		Connection connection = getConnection();

		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, state);
			ps.setInt(2, spaceId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 查询所有停车位的状态

	public static ArrayList<Parkinglot> selectAllSpaceState() {
		String sql = "select space_id,state from parking";

		Connection connection = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Parkinglot> arrayList = new ArrayList<>();
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Parkinglot parkinglot = new Parkinglot();
				parkinglot.setSpaceId(rs.getInt("space_id"));
				parkinglot.setState(rs.getString("state"));

				arrayList.add(parkinglot);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayList;

	}

	// 按照条件查询记录
	public static Record selectSingleCar(Record record) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("car_id,space_id,time_start, time_end,cost from record where 1=1 ");
		List<Object> list = appendWhereRecord(sql, record);

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;
		Car car = new Car();
		try {
			ps = connection.prepareStatement(sql.toString());

			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}

			rs = ps.executeQuery();

			while (rs.next()) {
				record.setCarId(rs.getString("car_id"));
				record.setSpaceId(rs.getInt("space_id"));
				record.setTimeStart(rs.getString("time_start"));
				record.setTimeEnd(rs.getString("time_end"));

				record.setCost(rs.getDouble("cost"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return record;

	}

	public static List<Object> appendWhereRecord(StringBuffer sql, Record record) {
		List<Object> list = new ArrayList<>();
		String carId = record.getCarId();
		if (carId != null && !carId.trim().isEmpty()) {
			sql.append("and car_id = ? ");
			list.add(carId);

		}
		int spaceId = record.getSpaceId();
		if (spaceId != 0) {
			sql.append("and space_id = ? ");
			list.add(spaceId);
		}
		double cost = record.getCost();
		if (cost != 0) {
			sql.append("and cost = ? ");
			list.add(cost);
		}

		return list;

	}



	// 添加取车时间

	public static int updateRecord(Record record) {
		String carId = record.getCarId();
		Integer spaceId = record.getSpaceId();

		String timeEnd = record.getTimeEnd();
		double cost = record.getCost();

		String sql = "update record set time_end=? ,cost =? where car_id =? and space_id=?";

		Connection connection = null;
		connection = getConnection();

		PreparedStatement ps = null;
		int flag = 0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, timeEnd);
			ps.setDouble(2, cost);
			ps.setString(3, carId);
			ps.setInt(4, spaceId);
			flag = ps.executeUpdate();

			System.out.println("记录更新");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("更新失败");
			e.printStackTrace();
		}

		exClose(null, ps, connection);
		return flag;

	}
	
	public static ArrayList<Record> findAllRecord() {
		String sql ="select * from  record";
		Connection connection = getConnection();
		
		PreparedStatement ps =null;
		ResultSet rs = null;
		ArrayList<Record> records = new ArrayList<>();
		try {
			ps= connection.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Record record = new Record();
				record.setCarId(rs.getString("car_id"));
				record.setSpaceId(rs.getInt("space_id"));
				record.setTimeStart(rs.getString("time_start"));
				record.setTimeEnd(rs.getString("time_end"));
				record.setCost(rs.getDouble("cost"));
				records.add(record);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		exClose(rs, ps, connection);
		
		return records;
		
		
		
	}

}